package com.dao.impl;


import com.dao.StockDao;
import com.entity.Stock;
import com.exception.CustomerException;
import com.result.QueryResult;
import com.util.DBUtil;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 服装库存管理接口实现类
 * Author Jay
 */
public class StockDaoImpl implements StockDao {
    /**
     * 根据id查询服装对象的方法
     * @param id 优惠券的编号
     * @return
     */
    @Override
    public Stock selectById(Integer id) {
        try{
            String sql="select `id` ,`code`,`type`,`img`,`brand`,`desc`,`counts_X`,`counts_M`," +
                    "`counts_L`,price,inUsed from stock where id=?";
            PreparedStatement preparedStatement= DBUtil.createPreparedStatement(sql);
            preparedStatement.setInt(1,id);
            ResultSet resultSet=preparedStatement.executeQuery();
            if(resultSet.next()){
                Stock stock=new Stock();
                stock.setId(resultSet.getInt("id"));
                stock.setCode(resultSet.getString("code"));
                stock.setType(resultSet.getString("type"));
                stock.setImg(resultSet.getString("img"));
                stock.setBrand(resultSet.getString("brand"));
                stock.setDesc(resultSet.getString("desc"));
                stock.setCountsX(resultSet.getInt("counts_X"));
                stock.setCountsM(resultSet.getInt("counts_M"));
                stock.setCountsL(resultSet.getInt("counts_L"));
                stock.setPrice(resultSet.getDouble("price"));
                stock.setInUsed(resultSet.getBoolean("inUsed"));
                return stock;
            }
            return null;
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("");
        }
    }


    /**
     * 查询全部服装对象的方法
     * @return 包含全部优惠券的集合
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Stock> selectAll() throws SQLException, ClassNotFoundException {
        try{
            String sql="select `id` ,`code`,`type`,`img`,`brand`,`desc`,`counts_X`,`counts_M`," +
                    "`counts_L`,price,inUsed from stock ";
            PreparedStatement preparedStatement= DBUtil.createPreparedStatement(sql);
            ResultSet resultSet=preparedStatement.executeQuery();
            List<Stock> stockList=new ArrayList<>();
            while(resultSet.next()){
                Stock stock=new Stock();
                stock.setId(resultSet.getInt("id"));
                stock.setCode(resultSet.getString("code"));
                stock.setType(resultSet.getString("type"));
                stock.setImg(resultSet.getString("img"));
                stock.setBrand(resultSet.getString("brand"));
                stock.setDesc(resultSet.getString("desc"));
                stock.setCountsX(resultSet.getInt("counts_X"));
                stock.setCountsM(resultSet.getInt("counts_M"));
                stock.setCountsL(resultSet.getInt("counts_L"));
                stock.setPrice(resultSet.getDouble("price"));
                stock.setInUsed(resultSet.getBoolean("inUsed"));
                stockList.add(stock);
            }
            return stockList;
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("");
        }
    }

    /**
     * 根据偏移量和每页记录数分页的服装信息
     * @param offset 0,10,20 偏移量
     * @param size 10 每页的记录数
     * @return
     */
    @Override
    public QueryResult selectByPage(Integer offset, Integer size) {
        try {
            QueryResult queryResult = new QueryResult();

            //1.查询页面数据
            String sql="select `id` ,`code`,`type`,`img`,`brand`,`desc`,`counts_X`,`counts_M`," +
                    "`counts_L`,price,inUsed from stock limit ?,?";
            PreparedStatement preparedStatement = DBUtil.createPreparedStatement(sql);

            //设置分页条件
            preparedStatement.setInt(1, offset);
            preparedStatement.setInt(2, size);

            ResultSet resultSet = DBUtil.execQuery(preparedStatement);
            List<Stock> stockList = new ArrayList<>();
            // 循环滚动游标
            while (resultSet.next()) {
                Stock stock=new Stock();
                stock.setId(resultSet.getInt("id"));
                stock.setCode(resultSet.getString("code"));
                stock.setType(resultSet.getString("type"));
                stock.setImg(resultSet.getString("img"));
                stock.setBrand(resultSet.getString("brand"));
                stock.setDesc(resultSet.getString("desc"));
                stock.setCountsX(resultSet.getInt("counts_X"));
                stock.setCountsM(resultSet.getInt("counts_M"));
                stock.setCountsL(resultSet.getInt("counts_L"));
                stock.setPrice(resultSet.getDouble("price"));
                stock.setInUsed(resultSet.getBoolean("inUsed"));
                stockList.add(stock);
            }

            queryResult.setList(stockList);

            //2.查询总记录数
            String sql2="select count(*) from stock";
            PreparedStatement preparedStatement2 = DBUtil.createPreparedStatement(sql2);
            ResultSet resultSet2 = DBUtil.execQuery(preparedStatement2);

            if(resultSet2.next()){
                queryResult.setCount(resultSet2.getLong(1));
            }

            return queryResult;

        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("查询失败!");
        }
    }

    @Override
    public List<Stock> selectLikeName(String name) {
        return null;
    }

    /**
     * 添加服装的方法
     * @param stock 优惠券对象, 不包含id特征(不需要,数据库自增)
     * @return
     */
    @Override
    public Integer insert(Stock stock) {
        try {
            String sql="insert into stock values(null,?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement preparedStatement=DBUtil.createPreparedStatement(sql);
            preparedStatement.setString(1,stock.getCode());
            preparedStatement.setString(2,stock.getType());
            preparedStatement.setString(3,stock.getImg());
            preparedStatement.setString(4,stock.getBrand());
            preparedStatement.setString(5,stock.getDesc());
            preparedStatement.setInt(6,stock.getCountsX());
            preparedStatement.setInt(7,stock.getCountsM());
            preparedStatement.setInt(8,stock.getCountsL());
            preparedStatement.setDouble(9,stock.getPrice());
            preparedStatement.setBoolean(10,stock.getInUsed());
            int i=DBUtil.execUpdate(preparedStatement);
            DBUtil.close();
            return i;
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("");
        }
    }

    /**
     * 根据id修改服装信息的方法
     * @param stock 包含id特征的服装对象
     */
    @Override
    public void update(Stock stock) {
        try{
            String sql="update stock set `id`=?,`code`=?,`type`=?,`img`=?,`brand`=?,`desc`=?,`counts_X`=?,`counts_M`=?,`counts_L`=?,`price`=?,`inUsed`=? where `id`=?";
            PreparedStatement preparedStatement=DBUtil.createPreparedStatement(sql);
            preparedStatement.setInt(1,stock.getId());
            preparedStatement.setString(2,stock.getCode());
            preparedStatement.setString(3,stock.getType());
            preparedStatement.setString(4,stock.getImg());
            preparedStatement.setString(5,stock.getBrand());
            preparedStatement.setString(6,stock.getDesc());
            preparedStatement.setInt(7,stock.getCountsX());
            preparedStatement.setInt(8,stock.getCountsM());
            preparedStatement.setInt(9,stock.getCountsL());
            preparedStatement.setDouble(10,stock.getPrice());
            preparedStatement.setBoolean(11,stock.getInUsed());
            preparedStatement.setInt(12,stock.getId());
            int i=DBUtil.execUpdate(preparedStatement);
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("");
        }
    }

    /**
     * 根据id删除服装
     * @param id 服装编号
     */
    @Override
    public void delete(Integer id) {
        try {
            String sql="delete from stock where id=?";
            PreparedStatement preparedStatement=DBUtil.createPreparedStatement(sql);
            preparedStatement.setInt(1,id);
            DBUtil.execUpdate(preparedStatement);
        }catch (Exception e){
            e.printStackTrace();
            throw new CustomerException("");
        }
    }

    /**
     * 修改上下架
     * @param id 服装编号
     * @param inUsed 上下架状态
     */
    @Override
    public void upper(Integer id, Boolean inUsed) {
        Stock stock=selectById(id);
        stock.setInUsed(inUsed);
        update(stock);
    }
}
